﻿using MySql.Data.MySqlClient;
using System;
using System.Data;
using System.Data.Common;
using System.IO;
using System.Linq;
using System.Text;
using ZhCun.DbCore.BuildSQLText;
using ZhCun.DbCore.Exceptions;

namespace ZhCun.DbCore.DBHelper
{
    public class MySqlDbHelper : DbHelperBase
    {
        public MySqlDbHelper(string connStr)
            : base(connStr)
        { }

        MySqlConnection _DBConnectionObj;
        MySqlCommand _DbCommandObj;
        MySqlDataAdapter _DbDataAdapterObj;

        public override DbConnection DBConnectionObj
        {
            get
            {
                //SqlBulkCopy aa = new SqlBulkCopy(new SqlConnection());
                if (_DBConnectionObj == null)
                {
                    _DBConnectionObj = new MySqlConnection(ConnStr);
                }
                return _DBConnectionObj;
            }
        }

        public override DbCommand DbCommandObj
        {
            get
            {
                if (_DbCommandObj == null)
                {
                    _DbCommandObj = new MySqlCommand();
                }
                return _DbCommandObj;
            }
        }

        protected internal override DbDataAdapter DbDataAdapterObj
        {
            get
            {
                if (_DbDataAdapterObj == null)
                {
                    _DbDataAdapterObj = new MySqlDataAdapter();
                }
                return _DbDataAdapterObj;
            }
        }

        public override DbParameter CreateDbParameter(string paramName, object paramValue)
        {
            if (paramName.Substring(0, 1) != BuildSQLBase.PARAMSIGN_MYSQL)
            {
                paramName = string.Format("{1}{0}", paramName, BuildSQLBase.PARAMSIGN_MYSQL);
            }
            return new MySqlParameter(paramName, paramValue);
        }

        /// <summary>  
        /// 将DataTable转换为标准的CSV  
        /// </summary>  
        /// <param name="table">数据表</param>  
        /// <returns>返回标准的CSV</returns>  
        private static string DataTableToCsv(DataTable table)
        {
            //以半角逗号（即,）作分隔符，列为空也要表达其存在。  
            //列内容如存在半角逗号（即,）则用半角引号（即""）将该字段值包含起来。  
            //列内容如存在半角引号（即"）则应替换成半角双引号（""）转义，并用半角引号（即""）将该字段值包含起来。  
            StringBuilder sb = new StringBuilder();
            DataColumn colum;
            foreach (DataRow row in table.Rows)
            {
                for (int i = 0; i < table.Columns.Count; i++)
                {
                    colum = table.Columns[i];
                    if (i != 0) sb.Append(",");
                    if (colum.DataType == typeof(string) && row[colum].ToString().Contains(","))
                    {
                        sb.Append("\"" + row[colum].ToString().Replace("\"", "\"\"") + "\"");
                    }
                    else sb.Append(row[colum].ToString());
                }
                sb.AppendLine();
            }
            return sb.ToString();
        }

        /// <summary>
        /// 批量拷贝插入
        /// </summary>
        public override int BulkInsert(DataTable dt)
        {
            string tmpPath = Path.GetTempFileName();
            try
            {
                string csv = DataTableToCsv(dt);

                //要与mysql的编码方式对象, 数据库要utf8, 表也一样
                using (StreamWriter sw = new StreamWriter(tmpPath, false, UTF8Encoding.UTF8))
                {
                    sw.Write(csv);
                    sw.Close();
                }
                MySqlBulkLoader bulk = new MySqlBulkLoader(_DBConnectionObj)
                {
                    FieldTerminator = ",",
                    FieldQuotationCharacter = '"',
                    EscapeCharacter = '"',
                    LineTerminator = "\r\n",
                    FileName = tmpPath,
                    NumberOfLinesToSkip = 0,
                    TableName = dt.TableName,    //也是mysql内表的名
                };
                //bulk.CharacterSet = "utf-8";
                bulk.Columns.AddRange(dt.Columns.Cast<DataColumn>().Select(colum => colum.ColumnName).ToList());

                OpenConnection();
                return bulk.Load();

            }
            catch (Exception ex)
            {
                var dbEx = new DbHelperException("mysql BulkInsert 发生异常", this, ex);
                throw dbEx;
            }
            finally
            {
                CloseConnect();
                File.Delete(tmpPath);
            }
        }
    }
}